<!DOCTYPE html>
<html>
<head hexo-theme='https://volantis.js.org/#'>
  <meta charset="utf-8">
  <!-- SEO相关 -->
  
    
  
  <!-- 渲染优化 -->
  <meta name="renderer" content="webkit">
  <meta name="force-rendering" content="webkit">
  <meta http-equiv="X-UA-Compatible" content="IE=Edge,chrome=1">
  <meta name="HandheldFriendly" content="True" >
  <meta name="apple-mobile-web-app-capable" content="yes">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <meta name="baidu-site-verification" content="hZiKlANZ5f" />
  <!-- 页面元数据 -->
  
    <title>MySQL笔记(2):查询练习 - CyChan&#39;s Blog</title>
  
    <meta name="keywords" content="MySQL">
  
  
    <meta name="description" content="mysql查询练习创建数据表">
  

  <!-- feed -->
  
    <link rel="alternate" href="/atom.xml" title="CyChan's Blog">
  

  <!-- import meta -->
  

  <!-- link -->
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free@5.13/css/all.min.css">
  
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3.5.7/dist/jquery.fancybox.min.css">

  
  
    
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/node-waves@0.7.6/dist/waves.min.css">

  

  
  <link rel="shortcut icon" type='image/x-icon' href="https://gitee.com/CyChan811/BlogImage/raw/master/img/头像.jpg">
  

  
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/aplayer@1.10/dist/APlayer.min.css">
  

  

  <!-- import link -->
  

  
  
    
<link rel="stylesheet" href="/css/style.css">

  

  <script>
    function setLoadingBarProgress(num) {
      document.getElementById('loading-bar').style.width=num+"%";
    }
  </script>

  
  
</head>

<body>
  
  <div id="loading-bar-wrapper">
  <div id="loading-bar"></div>
</div>
<header class="l_header shadow blur">
  <div class='container'>
  <div class='wrapper'>
    <div class='nav-sub'>
      <p class="title"></p>
      <ul class='switcher nav-list-h'>
        <li><a class="s-comment fas fa-comments fa-fw" target="_self" href='javascript:void(0)'></a></li>
        
          <li><a class="s-toc fas fa-list fa-fw" target="_self" href='javascript:void(0)'></a></li>
        
      </ul>
    </div>
		<div class="nav-main">
      
        
        <a class="title flat-box" target="_self" href='/'>
          
          
          
            CyChan
          
          
        </a>
      

			<div class='menu navigation'>
				<ul class='nav-list-h'>
          
          
          
            
            
              <li>
                <a class="flat-box" href=/
                  
                  
                  
                    id="home"
                  >
                  <i class='fas fa-home fa-fw'></i>首页
                </a>
                
              </li>
            
          
          
            
            
              <li>
                <a class="flat-box" href=/categories/
                  
                  
                  
                    id="categories"
                  >
                  <i class='fas fa-folder-open fa-fw'></i>分类
                </a>
                
              </li>
            
          
          
            
            
              <li>
                <a class="flat-box" href=/tags/
                  
                  
                  
                    id="tags"
                  >
                  <i class='fas fa-tags fa-fw'></i>标签
                </a>
                
              </li>
            
          
          
            
            
              <li>
                <a class="flat-box" href=/archives/
                  
                  
                  
                    id="archives"
                  >
                  <i class='fas fa-archive fa-fw'></i>归档
                </a>
                
              </li>
            
          
          
            
            
              <li>
                <a class="flat-box" href=/friends/
                  
                  
                  
                    id="friends"
                  >
                  <i class='fas fa-link fa-fw'></i>友链
                </a>
                
              </li>
            
          
          
            
            
              <li>
                <a class="flat-box" href=/Message/
                  
                  
                  
                    id="Message"
                  >
                  <i class='fas fa-comments fa-fw fa-fw'></i>留言板
                </a>
                
              </li>
            
          
          
            
            
              <li>
                <a class="flat-box" href=/about/
                  
                  
                  
                    id="about"
                  >
                  <i class='fas fa-info-circle fa-fw'></i>关于
                </a>
                
              </li>
            
          
          
				</ul>
			</div>

      <div class="m_search">
        <form name="searchform" class="form u-search-form">
          <i class="icon fas fa-search fa-fw"></i>
          <input type="text" class="input u-search-input" placeholder="Search..." />
        </form>
      </div>

			<ul class='switcher nav-list-h'>
				
					<li><a class="s-search fas fa-search fa-fw" target="_self" href='javascript:void(0)'></a></li>
				
				<li>
          <a class="s-menu fas fa-bars fa-fw" target="_self" href='javascript:void(0)'></a>
          <ul class="menu-phone list-v navigation white-box">
            
              
            
              <li>
                <a class="flat-box" href=/
                  
                  
                  
                    id="home"
                  >
                  <i class='fas fa-home fa-fw'></i>首页
                </a>
                
              </li>
            
          
            
              
            
              <li>
                <a class="flat-box" href=/categories/
                  
                  
                  
                    id="categories"
                  >
                  <i class='fas fa-folder-open fa-fw'></i>分类
                </a>
                
              </li>
            
          
            
              
            
              <li>
                <a class="flat-box" href=/tags/
                  
                  
                  
                    id="tags"
                  >
                  <i class='fas fa-tags fa-fw'></i>标签
                </a>
                
              </li>
            
          
            
              
            
              <li>
                <a class="flat-box" href=/archives/
                  
                  
                  
                    id="archives"
                  >
                  <i class='fas fa-archive fa-fw'></i>归档
                </a>
                
              </li>
            
          
            
              
            
              <li>
                <a class="flat-box" href=/friends/
                  
                  
                  
                    id="friends"
                  >
                  <i class='fas fa-link fa-fw'></i>友链
                </a>
                
              </li>
            
          
            
              
            
              <li>
                <a class="flat-box" href=/Message/
                  
                  
                  
                    id="Message"
                  >
                  <i class='fas fa-comments fa-fw fa-fw'></i>留言板
                </a>
                
              </li>
            
          
            
              
            
              <li>
                <a class="flat-box" href=/about/
                  
                  
                  
                    id="about"
                  >
                  <i class='fas fa-info-circle fa-fw'></i>关于
                </a>
                
              </li>
            
          
            
          </ul>
        </li>
			</ul>
		</div>
	</div>
  </div>
</header>

<script>setLoadingBarProgress(40);</script>



  <div class="l_body nocover">
    <div class='body-wrapper'>
      

<div class='l_main'>
  

  
    <article id="post" class="post white-box reveal shadow article-type-post" itemscope itemprop="blogPost">
      


  <section class='meta'>
    
      
      
      <div class="meta" id="header-meta">
        
          
  <h1 class="title">
    <a href="/2020/07/04/MySQL%E7%AC%94%E8%AE%B0-2-%E6%9F%A5%E8%AF%A2%E7%BB%83%E4%B9%A0/">
      MySQL笔记(2):查询练习
    </a>
  </h1>


        
        <div class='new-meta-box'>
          
            
          
            
              
<div class='new-meta-item author'>
  <a href="https://cychan811.gitee.io" rel="nofollow">
    <img src="https://gitee.com/CyChan811/BlogImage/raw/master/img/头像.jpg">
    <p>CyChan</p>
  </a>
</div>

            
          
            
              
  
  <div class='new-meta-item category'>
    <a href='/categories/%E5%90%8E%E5%8F%B0/' rel="nofollow">
      <i class="fas fa-folder-open fa-fw" aria-hidden="true"></i>
      <p>后台</p>
    </a>
  </div>


            
          
            
              <div class="new-meta-item date">
  <a class='notlink'>
    <i class="fas fa-calendar-alt fa-fw" aria-hidden="true"></i>
    <p>发布于：2020年7月4日</p>
  </a>
</div>

            
          
            
              

            
          
            
              
  <div class="new-meta-item browse valine">
    <a class='notlink'>
      <i class="fas fa-eye fa-fw" aria-hidden="true"></i>
      
      <span id="/2020/07/04/MySQL%E7%AC%94%E8%AE%B0-2-%E6%9F%A5%E8%AF%A2%E7%BB%83%E4%B9%A0/" class="leancloud_visitors" data-flag-title="MySQL笔记(2):查询练习">
      <p>
        <span class="leancloud-visitors-count"></span>
      </p>
      </span>
    </a>
  </div>


            
          
        </div>
        
          <hr>
        
      </div>
    
  </section>


      <section class="article typo">
        <div class="article-entry" itemprop="articleBody">
          
          
          <h2 id="mysql查询练习"><a href="#mysql查询练习" class="headerlink" title="mysql查询练习"></a>mysql查询练习</h2><h3 id="创建数据表"><a href="#创建数据表" class="headerlink" title="创建数据表"></a>创建数据表</h3><a id="more"></a>
<p>新建数据库:<code>create database select_test;</code><br>选择数据库:<code>use select_test;</code></p>
<p>– 学生表<br>– Student<br>– 学号<br>– 姓名<br>– 性别<br>– 出生年月日<br>– 所在班级</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> student(</span><br><span class="line">    sno <span class="built_in">varchar</span>(<span class="number">20</span>) primary <span class="keyword">key</span>,</span><br><span class="line">    sname <span class="built_in">varchar</span>(<span class="number">20</span>) <span class="keyword">not</span> <span class="literal">NULL</span>,</span><br><span class="line">    sgender <span class="built_in">varchar</span>(<span class="number">10</span>) <span class="keyword">not</span> <span class="literal">NULL</span>,</span><br><span class="line">    sbirthday datetime,</span><br><span class="line">    <span class="keyword">class</span> <span class="built_in">varchar</span>(<span class="number">20</span>)</span><br><span class="line">);</span><br></pre></td></tr></table></figure>

<p>– 教师表<br>– Teacher<br>– 教师编号<br>– 教师名字<br>– 教师性别<br>– 出生年月日<br>– 职称<br>– 所在部门</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> teacher(</span><br><span class="line">    tno <span class="built_in">varchar</span>(<span class="number">20</span>) primary <span class="keyword">key</span>,</span><br><span class="line">    tname <span class="built_in">varchar</span>(<span class="number">20</span>) <span class="keyword">not</span> <span class="literal">NULL</span>,</span><br><span class="line">    tgender <span class="built_in">varchar</span>(<span class="number">10</span>) <span class="keyword">not</span> <span class="literal">NULL</span>,</span><br><span class="line">    tbirthday <span class="built_in">varchar</span>(<span class="number">20</span>) <span class="keyword">not</span> <span class="literal">NULL</span>,</span><br><span class="line">    prof <span class="built_in">varchar</span>(<span class="number">20</span>) <span class="keyword">not</span> <span class="literal">NULL</span>,</span><br><span class="line">    depart <span class="built_in">varchar</span>(<span class="number">20</span>) <span class="keyword">not</span> <span class="literal">NULL</span></span><br><span class="line">);</span><br></pre></td></tr></table></figure>

<p>– 课程表<br>– Course<br>– 课程号<br>– 课程名称<br>– 教师编号</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> course(</span><br><span class="line">    cno <span class="built_in">varchar</span>(<span class="number">20</span>) primary <span class="keyword">key</span>,</span><br><span class="line">    cname <span class="built_in">varchar</span>(<span class="number">20</span>) <span class="keyword">not</span> <span class="literal">NULL</span>,</span><br><span class="line">    tno <span class="built_in">varchar</span>(<span class="number">20</span>) <span class="keyword">not</span> <span class="literal">NULL</span>,</span><br><span class="line">    <span class="keyword">foreign</span> <span class="keyword">key</span>(tno) <span class="keyword">references</span> teacher(tno)</span><br><span class="line">);</span><br></pre></td></tr></table></figure>

<p>– 成绩表<br>– Score<br>– 学号<br>– 课程号<br>–成绩</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> score(</span><br><span class="line">    sno <span class="built_in">varchar</span>(<span class="number">20</span>) <span class="keyword">not</span> <span class="literal">NULL</span>,</span><br><span class="line">    cno <span class="built_in">varchar</span>(<span class="number">20</span>) <span class="keyword">not</span> <span class="literal">NULL</span>,</span><br><span class="line">    degree <span class="built_in">decimal</span>,</span><br><span class="line">    <span class="keyword">foreign</span> <span class="keyword">key</span>(sno) <span class="keyword">references</span> student(sno),</span><br><span class="line">    <span class="keyword">foreign</span> <span class="keyword">key</span>(cno) <span class="keyword">references</span> course(cno),</span><br><span class="line">    primary <span class="keyword">key</span>(sno, cno)</span><br><span class="line">);</span><br></pre></td></tr></table></figure>

<h3 id="往数据表中添加数据"><a href="#往数据表中添加数据" class="headerlink" title="往数据表中添加数据"></a>往数据表中添加数据</h3><p>– 添加学生信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student <span class="keyword">values</span>(<span class="string">'101'</span>, <span class="string">'曾华'</span>, <span class="string">'男'</span>, <span class="string">'1977-09-01'</span>, <span class="string">'95033'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student <span class="keyword">values</span>(<span class="string">'102'</span>, <span class="string">'匡明'</span>, <span class="string">'男'</span>, <span class="string">'1975-10-02'</span>, <span class="string">'95031'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student <span class="keyword">values</span>(<span class="string">'103'</span>, <span class="string">'王丽'</span>, <span class="string">'女'</span>, <span class="string">'1976-01-23'</span>, <span class="string">'95033'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student <span class="keyword">values</span>(<span class="string">'104'</span>, <span class="string">'李军'</span>, <span class="string">'男'</span>, <span class="string">'1976-02-20'</span>, <span class="string">'95033'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student <span class="keyword">values</span>(<span class="string">'105'</span>, <span class="string">'王芳'</span>, <span class="string">'女'</span>, <span class="string">'1975-02-10'</span>, <span class="string">'95031'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student <span class="keyword">values</span>(<span class="string">'106'</span>, <span class="string">'陆君'</span>, <span class="string">'男'</span>, <span class="string">'1974-06-03'</span>, <span class="string">'95031'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student <span class="keyword">values</span>(<span class="string">'107'</span>, <span class="string">'王尼玛'</span>, <span class="string">'男'</span>, <span class="string">'1976-02-20'</span>, <span class="string">'95033'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student <span class="keyword">values</span>(<span class="string">'108'</span>, <span class="string">'张全蛋'</span>, <span class="string">'男'</span>, <span class="string">'1975-02-10'</span>, <span class="string">'95031'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student <span class="keyword">values</span>(<span class="string">'109'</span>, <span class="string">'赵铁柱'</span>, <span class="string">'男'</span>, <span class="string">'1974-06-03'</span>, <span class="string">'95031'</span>);</span><br></pre></td></tr></table></figure>

<p>–添加教师信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> teacher <span class="keyword">values</span>(<span class="string">'804'</span>, <span class="string">'李诚'</span>, <span class="string">'男'</span>, <span class="string">'1958-12-02'</span>, <span class="string">'副教授'</span>, <span class="string">'计算机系'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> teacher <span class="keyword">values</span>(<span class="string">'856'</span>, <span class="string">'张旭'</span>, <span class="string">'男'</span>, <span class="string">'1969-03-12'</span>, <span class="string">'讲师'</span>, <span class="string">'电子工程系'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> teacher <span class="keyword">values</span>(<span class="string">'825'</span>, <span class="string">'王萍'</span>, <span class="string">'女'</span>, <span class="string">'1972-05-05'</span>, <span class="string">'助教'</span>, <span class="string">'计算机系'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> teacher <span class="keyword">values</span>(<span class="string">'831'</span>, <span class="string">'刘冰'</span>, <span class="string">'女'</span>, <span class="string">'1977-08-14'</span>, <span class="string">'助教'</span>, <span class="string">'电子工程系'</span>);</span><br></pre></td></tr></table></figure>

<p>– 添加课程表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> course <span class="keyword">values</span>(<span class="string">'3-105'</span>, <span class="string">'计算机导论'</span>, <span class="string">'825'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> course <span class="keyword">values</span>(<span class="string">'3-245'</span>, <span class="string">'操作系统'</span>, <span class="string">'804'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> course <span class="keyword">values</span>(<span class="string">'6-166'</span>, <span class="string">'数字电路'</span>, <span class="string">'856'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> course <span class="keyword">values</span>(<span class="string">'9-888'</span>, <span class="string">'高等数学'</span>, <span class="string">'831'</span>);</span><br></pre></td></tr></table></figure>

<p>– 添加成绩表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> score <span class="keyword">values</span>(<span class="string">'103'</span>, <span class="string">'3-105'</span>, <span class="string">'92'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> score <span class="keyword">values</span>(<span class="string">'103'</span>, <span class="string">'6-166'</span>, <span class="string">'85'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> score <span class="keyword">values</span>(<span class="string">'103'</span>, <span class="string">'3-245'</span>, <span class="string">'86'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> score <span class="keyword">values</span>(<span class="string">'105'</span>, <span class="string">'3-105'</span>, <span class="string">'88'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> score <span class="keyword">values</span>(<span class="string">'105'</span>, <span class="string">'3-245'</span>, <span class="string">'75'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> score <span class="keyword">values</span>(<span class="string">'105'</span>, <span class="string">'6-166'</span>, <span class="string">'79'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> score <span class="keyword">values</span>(<span class="string">'109'</span>, <span class="string">'3-105'</span>, <span class="string">'76'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> score <span class="keyword">values</span>(<span class="string">'109'</span>, <span class="string">'3-245'</span>, <span class="string">'68'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> score <span class="keyword">values</span>(<span class="string">'109'</span>, <span class="string">'6-166'</span>, <span class="string">'81'</span>);</span><br></pre></td></tr></table></figure>

<h3 id="查询练习"><a href="#查询练习" class="headerlink" title="查询练习"></a>查询练习</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 1.查询student表的所有记录</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> student;</span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 2.查询student表中的所有记录的sname,sgender和class列</span></span><br><span class="line"><span class="keyword">select</span> sname, sgender, <span class="keyword">class</span> <span class="keyword">from</span> student;</span><br><span class="line">+<span class="comment">-----------+---------+-------+</span></span><br><span class="line">| sname     | sgender | class |</span><br><span class="line">+<span class="comment">-----------+---------+-------+</span></span><br><span class="line">| 曾华      | 男      | 95033 |</span><br><span class="line">| 匡明      | 男      | 95031 |</span><br><span class="line">| 王丽      | 女      | 95033 |</span><br><span class="line">| 李军      | 男      | 95033 |</span><br><span class="line">| 王芳      | 女      | 95031 |</span><br><span class="line">| 陆君      | 男      | 95031 |</span><br><span class="line">| 王尼玛    | 男      | 95033 |</span><br><span class="line">| 张全蛋    | 男      | 95031 |</span><br><span class="line">| 赵铁柱    | 男      | 95031 |</span><br><span class="line">+<span class="comment">-----------+---------+-------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 3.查询教师所有的单位即不重复的depart列</span></span><br><span class="line"><span class="comment">-- distinct:有区别的,不同种类的  用来排除重复的</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> depart <span class="keyword">from</span> teacher;</span><br><span class="line">+<span class="comment">-----------------+</span></span><br><span class="line">| depart          |</span><br><span class="line">+<span class="comment">-----------------+</span></span><br><span class="line">| 计算机系         |</span><br><span class="line">| 电子工程系        |</span><br><span class="line">+<span class="comment">-----------------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 4.查询score表中成绩在60到80之间的所有记录</span></span><br><span class="line"><span class="comment">-- 注意:betweent ... and ... 是包含边界值的</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> score <span class="keyword">where</span> degree <span class="keyword">between</span> <span class="number">60</span> <span class="keyword">and</span> <span class="number">80</span>;</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| sno | cno   | degree |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| 105 | 3-245 |     75 |</span><br><span class="line">| 105 | 6-166 |     79 |</span><br><span class="line">| 109 | 3-105 |     76 |</span><br><span class="line">| 109 | 3-245 |     68 |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 或:直接使用运算符比较</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> score <span class="keyword">where</span> degree &gt; <span class="number">60</span> <span class="keyword">and</span> degree &lt; <span class="number">80</span>;</span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 5.查询score表中成绩为85,86和88的记录</span></span><br><span class="line"><span class="comment">-- 表示或者关系的查询 in</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> score <span class="keyword">where</span> degree <span class="keyword">in</span>(<span class="number">85</span>, <span class="number">86</span>, <span class="number">88</span>);</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| sno | cno   | degree |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| 103 | 3-245 |     86 |</span><br><span class="line">| 103 | 6-166 |     85 |</span><br><span class="line">| 105 | 3-105 |     88 |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 6.查询student表中"95031"班或性别为"女"的同学记录</span></span><br><span class="line"><span class="comment">-- or 表示或者</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> student <span class="keyword">where</span> <span class="keyword">class</span> = <span class="string">'95031'</span> <span class="keyword">or</span> sgender = <span class="string">'女'</span>;</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br><span class="line">| sno | sname     | sgender | sbirthday           | class |</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br><span class="line">| 102 | 匡明      | 男      | 1975-10-02 00:00:00 | 95031 |</span><br><span class="line">| 103 | 王丽      | 女      | 1976-01-23 00:00:00 | 95033 |</span><br><span class="line">| 105 | 王芳      | 女      | 1975-02-10 00:00:00 | 95031 |</span><br><span class="line">| 106 | 陆君      | 男      | 1974-06-03 00:00:00 | 95031 |</span><br><span class="line">| 108 | 张全蛋    | 男      | 1975-02-10 00:00:00 | 95031 |</span><br><span class="line">| 109 | 赵铁柱    | 男      | 1974-06-03 00:00:00 | 95031 |</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 7.以class降序查询student表的所有记录</span></span><br><span class="line"><span class="comment">-- 降序: desc</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> student <span class="keyword">order</span> <span class="keyword">by</span> <span class="keyword">class</span> <span class="keyword">desc</span>;</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br><span class="line">| sno | sname     | sgender | sbirthday           | class |</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br><span class="line">| 101 | 曾华      | 男      | 1977-09-01 00:00:00 | 95033 |</span><br><span class="line">| 103 | 王丽      | 女      | 1976-01-23 00:00:00 | 95033 |</span><br><span class="line">| 104 | 李军      | 男      | 1976-02-20 00:00:00 | 95033 |</span><br><span class="line">| 107 | 王尼玛    | 男      | 1976-02-20 00:00:00 | 95033 |</span><br><span class="line">| 102 | 匡明      | 男      | 1975-10-02 00:00:00 | 95031 |</span><br><span class="line">| 105 | 王芳      | 女      | 1975-02-10 00:00:00 | 95031 |</span><br><span class="line">| 106 | 陆君      | 男      | 1974-06-03 00:00:00 | 95031 |</span><br><span class="line">| 108 | 张全蛋    | 男      | 1975-02-10 00:00:00 | 95031 |</span><br><span class="line">| 109 | 赵铁柱    | 男      | 1974-06-03 00:00:00 | 95031 |</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 升序: asc 默认是升序排列的,所以一般情况下不写asc;</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> student <span class="keyword">order</span> <span class="keyword">by</span> <span class="keyword">class</span>;</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br><span class="line">| sno | sname     | sgender | sbirthday           | class |</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br><span class="line">| 102 | 匡明      | 男      | 1975-10-02 00:00:00 | 95031 |</span><br><span class="line">| 105 | 王芳      | 女      | 1975-02-10 00:00:00 | 95031 |</span><br><span class="line">| 106 | 陆君      | 男      | 1974-06-03 00:00:00 | 95031 |</span><br><span class="line">| 108 | 张全蛋    | 男      | 1975-02-10 00:00:00 | 95031 |</span><br><span class="line">| 109 | 赵铁柱    | 男      | 1974-06-03 00:00:00 | 95031 |</span><br><span class="line">| 101 | 曾华      | 男      | 1977-09-01 00:00:00 | 95033 |</span><br><span class="line">| 103 | 王丽      | 女      | 1976-01-23 00:00:00 | 95033 |</span><br><span class="line">| 104 | 李军      | 男      | 1976-02-20 00:00:00 | 95033 |</span><br><span class="line">| 107 | 王尼玛    | 男      | 1976-02-20 00:00:00 | 95033 |</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 8.以cno升序,degree降序查询score表的所有记录</span></span><br><span class="line"><span class="comment">-- 先cno升序,重复的情况下再degree降序</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> score <span class="keyword">order</span> <span class="keyword">by</span> cno <span class="keyword">asc</span>, degree <span class="keyword">desc</span>;</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| sno | cno   | degree |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| 103 | 3-105 |     92 |</span><br><span class="line">| 105 | 3-105 |     88 |</span><br><span class="line">| 109 | 3-105 |     76 |</span><br><span class="line">| 103 | 3-245 |     86 |</span><br><span class="line">| 105 | 3-245 |     75 |</span><br><span class="line">| 109 | 3-245 |     68 |</span><br><span class="line">| 103 | 6-166 |     85 |</span><br><span class="line">| 109 | 6-166 |     81 |</span><br><span class="line">| 105 | 6-166 |     79 |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 9.查询"95031"班的学生人数</span></span><br><span class="line"><span class="comment">-- 统计 count</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">count</span>(*) <span class="keyword">from</span> student <span class="keyword">where</span> <span class="keyword">class</span> = <span class="string">'95031'</span>;</span><br><span class="line">+<span class="comment">----------+</span></span><br><span class="line">| count(*) |</span><br><span class="line">+<span class="comment">----------+</span></span><br><span class="line">|        5 |</span><br><span class="line">+<span class="comment">----------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 10.查询score表中的最高分的学生学号和课程号.(子查询或者排序)</span></span><br><span class="line"><span class="keyword">select</span> sno, cno <span class="keyword">from</span> score <span class="keyword">where</span> degree = (<span class="keyword">select</span> <span class="keyword">max</span>(degree) <span class="keyword">from</span> score);</span><br><span class="line">+<span class="comment">-----+-------+</span></span><br><span class="line">| sno | cno   |</span><br><span class="line">+<span class="comment">-----+-------+</span></span><br><span class="line">| 103 | 3-105 |</span><br><span class="line">+<span class="comment">-----+-------+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 子查询方式:</span></span><br><span class="line"><span class="comment">-- 1. 找到最高分</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">max</span>(degree) <span class="keyword">from</span> score;</span><br><span class="line"><span class="comment">--2. 找最高分的 sno 和 cno</span></span><br><span class="line"><span class="keyword">select</span> sno, cno <span class="keyword">from</span> score <span class="keyword">where</span> degree = (<span class="keyword">select</span> <span class="keyword">max</span>(degree) <span class="keyword">from</span> score);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 排序方式:(最高分有多个的情况下可能有数据问题)</span></span><br><span class="line"><span class="keyword">select</span> sno, cno, degree <span class="keyword">from</span> score <span class="keyword">order</span> <span class="keyword">by</span> degree <span class="keyword">desc</span>;</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| sno | cno   | degree |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| 103 | 3-105 |     92 |</span><br><span class="line">| 105 | 3-105 |     88 |</span><br><span class="line">| 103 | 3-245 |     86 |</span><br><span class="line">| 103 | 6-166 |     85 |</span><br><span class="line">| 109 | 6-166 |     81 |</span><br><span class="line">| 105 | 6-166 |     79 |</span><br><span class="line">| 109 | 3-105 |     76 |</span><br><span class="line">| 105 | 3-245 |     75 |</span><br><span class="line">| 109 | 3-245 |     68 |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- limit 第一个数字表示从多少开始  第二个数字表示查多少条数据</span></span><br><span class="line"><span class="keyword">select</span> sno, cno, degree <span class="keyword">from</span> score <span class="keyword">order</span> <span class="keyword">by</span> degree <span class="keyword">desc</span> <span class="keyword">limit</span> <span class="number">0</span>, <span class="number">1</span>;</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| sno | cno   | degree |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| 103 | 3-105 |     92 |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 11.查询每门课的平均成绩</span></span><br><span class="line"><span class="comment">-- avg()</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">avg</span>(degree) <span class="keyword">from</span> score <span class="keyword">where</span> cno = <span class="string">'3-105'</span>;</span><br><span class="line">+<span class="comment">-------------+</span></span><br><span class="line">| avg(degree) |</span><br><span class="line">+<span class="comment">-------------+</span></span><br><span class="line">|     85.3333 |</span><br><span class="line">+<span class="comment">-------------+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 我能不能在一个 sql 语句中写呢?而不是一条一条地写</span></span><br><span class="line"><span class="comment">-- group by 分组</span></span><br><span class="line"><span class="keyword">select</span> cno, <span class="keyword">avg</span>(degree) <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> cno;</span><br><span class="line">+<span class="comment">-------+-------------+</span></span><br><span class="line">| cno   | avg(degree) |</span><br><span class="line">+<span class="comment">-------+-------------+</span></span><br><span class="line">| 3-105 |     85.3333 |</span><br><span class="line">| 3-245 |     76.3333 |</span><br><span class="line">| 6-166 |     81.6667 |</span><br><span class="line">+<span class="comment">-------+-------------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 12.查询score表中至少有2名学生选修的并以3开头的课程的平均分数</span></span><br><span class="line"><span class="keyword">select</span> cno <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> cno;</span><br><span class="line">+<span class="comment">-------+</span></span><br><span class="line">| cno   |</span><br><span class="line">+<span class="comment">-------+</span></span><br><span class="line">| 3-105 |</span><br><span class="line">| 3-245 |</span><br><span class="line">| 6-166 |</span><br><span class="line">+<span class="comment">-------+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- having 表示特有</span></span><br><span class="line"><span class="keyword">select</span> cno <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> cno</span><br><span class="line"><span class="keyword">having</span> <span class="keyword">count</span>(cno) &gt;= <span class="number">2</span>;</span><br><span class="line">+<span class="comment">-------+</span></span><br><span class="line">| cno   |</span><br><span class="line">+<span class="comment">-------+</span></span><br><span class="line">| 3-105 |</span><br><span class="line">| 3-245 |</span><br><span class="line">| 6-166 |</span><br><span class="line">+<span class="comment">-------+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- like 模糊查询,'%'是一个通配符,匹配'3'后面的任意字符</span></span><br><span class="line"><span class="keyword">select</span> cno <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> cno</span><br><span class="line"><span class="keyword">having</span> <span class="keyword">count</span>(cno) &gt;= <span class="number">2</span> </span><br><span class="line"><span class="keyword">and</span> cno <span class="keyword">like</span> <span class="string">'3%'</span>;</span><br><span class="line">+<span class="comment">-------+</span></span><br><span class="line">| cno   |</span><br><span class="line">+<span class="comment">-------+</span></span><br><span class="line">| 3-105 |</span><br><span class="line">| 3-245 |</span><br><span class="line">+<span class="comment">-------+</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> cno, <span class="keyword">avg</span>(degree) <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> cno</span><br><span class="line"><span class="keyword">having</span> <span class="keyword">count</span>(cno) &gt;= <span class="number">2</span> <span class="keyword">and</span> cno <span class="keyword">like</span> <span class="string">'3%'</span>;</span><br><span class="line">+<span class="comment">-------+-------------+</span></span><br><span class="line">| cno   | avg(degree) |</span><br><span class="line">+<span class="comment">-------+-------------+</span></span><br><span class="line">| 3-105 |     85.3333 |</span><br><span class="line">| 3-245 |     76.3333 |</span><br><span class="line">+<span class="comment">-------+-------------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 13.查询分数大于70,小于90的sno列</span></span><br><span class="line"><span class="keyword">select</span> sno, degree <span class="keyword">from</span> score <span class="keyword">where</span> degree &gt; <span class="number">70</span> <span class="keyword">and</span> degree &lt; <span class="number">90</span>;</span><br><span class="line">+<span class="comment">-----+--------+</span></span><br><span class="line">| sno | degree |</span><br><span class="line">+<span class="comment">-----+--------+</span></span><br><span class="line">| 103 |     86 |</span><br><span class="line">| 103 |     85 |</span><br><span class="line">| 105 |     88 |</span><br><span class="line">| 105 |     75 |</span><br><span class="line">| 105 |     79 |</span><br><span class="line">| 109 |     76 |</span><br><span class="line">| 109 |     81 |</span><br><span class="line">+<span class="comment">-----+--------+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 再次提醒,between ... and ... 包含了边界值</span></span><br><span class="line"><span class="keyword">select</span> sno, degree <span class="keyword">from</span> score <span class="keyword">where</span> degree <span class="keyword">between</span> <span class="number">71</span> <span class="keyword">and</span> <span class="number">89</span>;</span><br></pre></td></tr></table></figure>
<p><strong>多表查询1:</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 14.查询所有学生的sname, cno 和 degree列</span></span><br><span class="line"><span class="comment">-- 先分别查询</span></span><br><span class="line"><span class="keyword">select</span> sno, sname <span class="keyword">from</span> student;</span><br><span class="line">+<span class="comment">-----+-----------+</span></span><br><span class="line">| sno | sname     |</span><br><span class="line">+<span class="comment">-----+-----------+</span></span><br><span class="line">| 101 | 曾华      |</span><br><span class="line">| 102 | 匡明      |</span><br><span class="line">| 103 | 王丽      |</span><br><span class="line">| 104 | 李军      |</span><br><span class="line">| 105 | 王芳      |</span><br><span class="line">| 106 | 陆君      |</span><br><span class="line">| 107 | 王尼玛    |</span><br><span class="line">| 108 | 张全蛋    |</span><br><span class="line">| 109 | 赵铁柱    |</span><br><span class="line">+<span class="comment">-----+-----------+</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> sno, cno, degree <span class="keyword">from</span> score;</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| sno | cno   | degree |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| 103 | 3-105 |     92 |</span><br><span class="line">| 103 | 3-245 |     86 |</span><br><span class="line">| 103 | 6-166 |     85 |</span><br><span class="line">| 105 | 3-105 |     88 |</span><br><span class="line">| 105 | 3-245 |     75 |</span><br><span class="line">| 105 | 6-166 |     79 |</span><br><span class="line">| 109 | 3-105 |     76 |</span><br><span class="line">| 109 | 3-245 |     68 |</span><br><span class="line">| 109 | 6-166 |     81 |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 多表查询</span></span><br><span class="line"><span class="keyword">select</span> sname, cno, degree <span class="keyword">from</span> student, score <span class="keyword">where</span> student.sno = score.sno;</span><br><span class="line">+<span class="comment">-----------+-------+--------+</span></span><br><span class="line">| sname     | cno   | degree |</span><br><span class="line">+<span class="comment">-----------+-------+--------+</span></span><br><span class="line">| 王丽      | 3-105 |     92 |</span><br><span class="line">| 王丽      | 3-245 |     86 |</span><br><span class="line">| 王丽      | 6-166 |     85 |</span><br><span class="line">| 王芳      | 3-105 |     88 |</span><br><span class="line">| 王芳      | 3-245 |     75 |</span><br><span class="line">| 王芳      | 6-166 |     79 |</span><br><span class="line">| 赵铁柱    | 3-105 |     76 |</span><br><span class="line">| 赵铁柱    | 3-245 |     68 |</span><br><span class="line">| 赵铁柱    | 6-166 |     81 |</span><br><span class="line">+<span class="comment">-----------+-------+--------+</span></span><br></pre></td></tr></table></figure>
<p><strong>多表查询2:</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 15.查询所有学生的sno,cname和degree列</span></span><br><span class="line"><span class="keyword">select</span> sno, cname, degree <span class="keyword">from</span> course, score <span class="keyword">where</span> course.cno = score.cno;</span><br><span class="line">+<span class="comment">-----+-----------------+--------+</span></span><br><span class="line">| sno | cname           | degree |</span><br><span class="line">+<span class="comment">-----+-----------------+--------+</span></span><br><span class="line">| 103 | 计算机导论      |     92 |</span><br><span class="line">| 103 | 操作系统        |     86 |</span><br><span class="line">| 103 | 数字电路        |     85 |</span><br><span class="line">| 105 | 计算机导论      |     88 |</span><br><span class="line">| 105 | 操作系统        |     75 |</span><br><span class="line">| 105 | 数字电路        |     79 |</span><br><span class="line">| 109 | 计算机导论      |     76 |</span><br><span class="line">| 109 | 操作系统        |     68 |</span><br><span class="line">| 109 | 数字电路        |     81 |</span><br><span class="line">+<span class="comment">-----+-----------------+--------+</span></span><br></pre></td></tr></table></figure>
<p><strong>三表关联查询:</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 16.查询所有学生的sname, cname和degree列</span></span><br><span class="line"><span class="comment">-- sname-&gt;student</span></span><br><span class="line"><span class="comment">-- cname-&gt;course</span></span><br><span class="line"><span class="comment">-- degree-&gt;score</span></span><br><span class="line"><span class="keyword">select</span> sname, cname, degree <span class="keyword">from</span> student, course, score <span class="keyword">where</span> student.sno = score.sno </span><br><span class="line"><span class="keyword">and</span> course.cno = score.cno;</span><br><span class="line">+<span class="comment">-----------+-----------------+--------+</span></span><br><span class="line">| sname     | cname           | degree |</span><br><span class="line">+<span class="comment">-----------+-----------------+--------+</span></span><br><span class="line">| 王丽      | 计算机导论      |     92 |</span><br><span class="line">| 王丽      | 操作系统        |     86 |</span><br><span class="line">| 王丽      | 数字电路        |     85 |</span><br><span class="line">| 王芳      | 计算机导论      |     88 |</span><br><span class="line">| 王芳      | 操作系统        |     75 |</span><br><span class="line">| 王芳      | 数字电路        |     79 |</span><br><span class="line">| 赵铁柱    | 计算机导论      |     76 |</span><br><span class="line">| 赵铁柱    | 操作系统        |     68 |</span><br><span class="line">| 赵铁柱    | 数字电路        |     81 |</span><br><span class="line">+<span class="comment">-----------+-----------------+--------+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 有歧义的字段要加作用域</span></span><br><span class="line"><span class="keyword">select</span> sname, cname, degree, student.sno, course.cno </span><br><span class="line"><span class="keyword">from</span> student, course, score </span><br><span class="line"><span class="keyword">where</span> student.sno = score.sno </span><br><span class="line"><span class="keyword">and</span> course.cno = score.cno;</span><br><span class="line">+<span class="comment">-----------+-----------------+--------+-----+-------+</span></span><br><span class="line">| sname     | cname           | degree | sno | cno   |</span><br><span class="line">+<span class="comment">-----------+-----------------+--------+-----+-------+</span></span><br><span class="line">| 王丽      | 计算机导论      |     92 | 103 | 3-105 |</span><br><span class="line">| 王丽      | 操作系统        |     86 | 103 | 3-245 |</span><br><span class="line">| 王丽      | 数字电路        |     85 | 103 | 6-166 |</span><br><span class="line">| 王芳      | 计算机导论      |     88 | 105 | 3-105 |</span><br><span class="line">| 王芳      | 操作系统        |     75 | 105 | 3-245 |</span><br><span class="line">| 王芳      | 数字电路        |     79 | 105 | 6-166 |</span><br><span class="line">| 赵铁柱    | 计算机导论      |     76 | 109 | 3-105 |</span><br><span class="line">| 赵铁柱    | 操作系统        |     68 | 109 | 3-245 |</span><br><span class="line">| 赵铁柱    | 数字电路        |     81 | 109 | 6-166 |</span><br><span class="line">+<span class="comment">-----------+-----------------+--------+-----+-------+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 为sno和cno取别名</span></span><br><span class="line"><span class="keyword">select</span> sname, cname, degree, student.sno <span class="keyword">as</span> stu_sno, course.cno <span class="keyword">as</span> cou_no</span><br><span class="line"><span class="keyword">from</span> student, course, score </span><br><span class="line"><span class="keyword">where</span> student.sno = score.sno </span><br><span class="line"><span class="keyword">and</span> course.cno = score.cno;</span><br><span class="line">+<span class="comment">-----------+-----------------+--------+---------+--------+</span></span><br><span class="line">| sname     | cname           | degree | stu_sno | cou_no |</span><br><span class="line">+<span class="comment">-----------+-----------------+--------+---------+--------+</span></span><br><span class="line">| 王丽      | 计算机导论      |     92 | 103     | 3-105  |</span><br><span class="line">| 王丽      | 操作系统        |     86 | 103     | 3-245  |</span><br><span class="line">| 王丽      | 数字电路        |     85 | 103     | 6-166  |</span><br><span class="line">| 王芳      | 计算机导论      |     88 | 105     | 3-105  |</span><br><span class="line">| 王芳      | 操作系统        |     75 | 105     | 3-245  |</span><br><span class="line">| 王芳      | 数字电路        |     79 | 105     | 6-166  |</span><br><span class="line">| 赵铁柱    | 计算机导论      |     76 | 109     | 3-105  |</span><br><span class="line">| 赵铁柱    | 操作系统        |     68 | 109     | 3-245  |</span><br><span class="line">| 赵铁柱    | 数字电路        |     81 | 109     | 6-166  |</span><br><span class="line">+<span class="comment">-----------+-----------------+--------+---------+--------+</span></span><br></pre></td></tr></table></figure>
<p><strong>子查询 加 分组:</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 17.查询'95031'班学生每门课的平均分</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> student <span class="keyword">where</span> <span class="keyword">class</span> = <span class="string">'95031'</span>;</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br><span class="line">| sno | sname     | sgender | sbirthday           | class |</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br><span class="line">| 102 | 匡明      | 男      | 1975-10-02 00:00:00 | 95031 |</span><br><span class="line">| 105 | 王芳      | 女      | 1975-02-10 00:00:00 | 95031 |</span><br><span class="line">| 106 | 陆君      | 男      | 1974-06-03 00:00:00 | 95031 |</span><br><span class="line">| 108 | 张全蛋    | 男      | 1975-02-10 00:00:00 | 95031 |</span><br><span class="line">| 109 | 赵铁柱    | 男      | 1974-06-03 00:00:00 | 95031 |</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 子查询</span></span><br><span class="line"><span class="comment">-- 用到了in表示或者关系的条件</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> score <span class="keyword">where</span> sno <span class="keyword">in</span>(<span class="keyword">select</span> sno <span class="keyword">from</span> student <span class="keyword">where</span> <span class="keyword">class</span> = <span class="string">'95031'</span>);</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| sno | cno   | degree |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| 105 | 3-105 |     88 |</span><br><span class="line">| 105 | 3-245 |     75 |</span><br><span class="line">| 105 | 6-166 |     79 |</span><br><span class="line">| 109 | 3-105 |     76 |</span><br><span class="line">| 109 | 3-245 |     68 |</span><br><span class="line">| 109 | 6-166 |     81 |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 分组求平均分</span></span><br><span class="line"><span class="keyword">select</span> cno, <span class="keyword">avg</span>(degree) <span class="keyword">from</span> score <span class="keyword">where</span> sno <span class="keyword">in</span>(<span class="keyword">select</span> sno <span class="keyword">from</span> student <span class="keyword">where</span> <span class="keyword">class</span> = <span class="string">'95031'</span>) <span class="keyword">group</span> <span class="keyword">by</span> cno;</span><br><span class="line">+<span class="comment">-------+-------------+</span></span><br><span class="line">| cno   | avg(degree) |</span><br><span class="line">+<span class="comment">-------+-------------+</span></span><br><span class="line">| 3-105 |     82.0000 |</span><br><span class="line">| 3-245 |     71.5000 |</span><br><span class="line">| 6-166 |     80.0000 |</span><br><span class="line">+<span class="comment">-------+-------------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 18.查询选修'3-105'课程的成绩高于'109'号同学'3-105'成绩的所有同学的记录</span></span><br><span class="line"><span class="keyword">select</span> degree <span class="keyword">from</span> score <span class="keyword">where</span> sno = <span class="string">'109'</span> <span class="keyword">and</span> cno = <span class="string">'3-105'</span>;</span><br><span class="line">+<span class="comment">--------+</span></span><br><span class="line">| degree |</span><br><span class="line">+<span class="comment">--------+</span></span><br><span class="line">|     76 |</span><br><span class="line">+<span class="comment">--------+</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> score <span class="keyword">where</span> degree &gt; (<span class="keyword">select</span> degree <span class="keyword">from</span> score <span class="keyword">where</span> sno = <span class="string">'109'</span> <span class="keyword">and</span> cno = <span class="string">'3-105'</span>) <span class="keyword">and</span> cno = <span class="string">'3-105'</span>;</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| sno | cno   | degree |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| 103 | 3-105 |     92 |</span><br><span class="line">| 105 | 3-105 |     88 |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 19.查询成绩高于学号为'109',课程号为'3-105'的成绩的所有记录</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> score <span class="keyword">where</span> degree &gt; (<span class="keyword">select</span> degree <span class="keyword">from</span> score <span class="keyword">where</span> sno = <span class="string">'109'</span> <span class="keyword">and</span> cno = <span class="string">'3-105'</span>);</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| sno | cno   | degree |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| 103 | 3-105 |     92 |</span><br><span class="line">| 103 | 3-245 |     86 |</span><br><span class="line">| 103 | 6-166 |     85 |</span><br><span class="line">| 105 | 3-105 |     88 |</span><br><span class="line">| 105 | 6-166 |     79 |</span><br><span class="line">| 109 | 6-166 |     81 |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 20.查询和学号为108,101的同学同年出生的所有同学的sno,sname和sbirthday列</span></span><br><span class="line"><span class="comment">-- year() 提取年份</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">year</span>(sbirthday) <span class="keyword">from</span> student <span class="keyword">where</span> sno <span class="keyword">in</span> (<span class="number">108</span>, <span class="number">101</span>);</span><br><span class="line">+<span class="comment">-----------------+</span></span><br><span class="line">| year(sbirthday) |</span><br><span class="line">+<span class="comment">-----------------+</span></span><br><span class="line">|            1977 |</span><br><span class="line">|            1975 |</span><br><span class="line">+<span class="comment">-----------------+</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> student <span class="keyword">where</span> <span class="keyword">year</span>(sbirthday) <span class="keyword">in</span>(<span class="keyword">select</span> <span class="keyword">year</span>(sbirthday) <span class="keyword">from</span> student <span class="keyword">where</span> sno <span class="keyword">in</span> (<span class="number">108</span>, <span class="number">101</span>));</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br><span class="line">| sno | sname     | sgender | sbirthday           | class |</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br><span class="line">| 101 | 曾华      | 男      | 1977-09-01 00:00:00 | 95033 |</span><br><span class="line">| 102 | 匡明      | 男      | 1975-10-02 00:00:00 | 95031 |</span><br><span class="line">| 105 | 王芳      | 女      | 1975-02-10 00:00:00 | 95031 |</span><br><span class="line">| 108 | 张全蛋    | 男      | 1975-02-10 00:00:00 | 95031 |</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 21.查询"张旭"教师任课的学生成绩</span></span><br><span class="line"><span class="comment">-- 嵌套子查询</span></span><br><span class="line"><span class="keyword">select</span> tno <span class="keyword">from</span> teacher <span class="keyword">where</span> tname = <span class="string">"张旭"</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> cno <span class="keyword">from</span> course <span class="keyword">where</span> tno = (<span class="keyword">select</span> tno <span class="keyword">from</span> teacher <span class="keyword">where</span> tname = <span class="string">"张旭"</span>);</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> score <span class="keyword">where</span> cno = (<span class="keyword">select</span> cno <span class="keyword">from</span> course <span class="keyword">where</span> tno = (<span class="keyword">select</span> tno <span class="keyword">from</span> teacher <span class="keyword">where</span> tname = <span class="string">"张旭"</span>));</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| sno | cno   | degree |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| 103 | 6-166 |     85 |</span><br><span class="line">| 105 | 6-166 |     79 |</span><br><span class="line">| 109 | 6-166 |     81 |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 22.查询选修某课程的同学人数多于5人的教师姓名</span></span><br><span class="line"><span class="comment">-- 一开始的时候没有符合条件的数据,所以添加一些数据先</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> score <span class="keyword">values</span>(<span class="string">'101'</span>, <span class="string">'3-105'</span>, <span class="string">'90'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> score <span class="keyword">values</span>(<span class="string">'102'</span>, <span class="string">'3-105'</span>, <span class="string">'91'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> score <span class="keyword">values</span>(<span class="string">'104'</span>, <span class="string">'3-105'</span>, <span class="string">'89'</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 先查找学生人数大于5的课程号</span></span><br><span class="line"><span class="keyword">select</span> cno <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> cno <span class="keyword">having</span> <span class="keyword">count</span>(*) &gt; <span class="number">5</span>;</span><br><span class="line">+<span class="comment">-------+</span></span><br><span class="line">| cno   |</span><br><span class="line">+<span class="comment">-------+</span></span><br><span class="line">| 3-105 |</span><br><span class="line">+<span class="comment">-------+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 再找到教这些课程的教师号</span></span><br><span class="line"><span class="comment">-- 注意用"in"更合适,因为可能不止一个老师符合条件</span></span><br><span class="line"><span class="keyword">select</span> tno <span class="keyword">from</span> course <span class="keyword">where</span> cno <span class="keyword">in</span>(<span class="keyword">select</span> cno <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> cno <span class="keyword">having</span> <span class="keyword">count</span>(*) &gt; <span class="number">5</span>);</span><br><span class="line">+<span class="comment">-----+</span></span><br><span class="line">| tno |</span><br><span class="line">+<span class="comment">-----+</span></span><br><span class="line">| 825 |</span><br><span class="line">+<span class="comment">-----+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 最后找到该教师号对应的教师名字</span></span><br><span class="line"><span class="keyword">select</span> tname <span class="keyword">from</span> teacher <span class="keyword">where</span> tno <span class="keyword">in</span>(<span class="keyword">select</span> tno <span class="keyword">from</span> course <span class="keyword">where</span> cno <span class="keyword">in</span>(<span class="keyword">select</span> cno <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> cno <span class="keyword">having</span> <span class="keyword">count</span>(*) &gt; <span class="number">5</span>));</span><br><span class="line">+<span class="comment">--------+</span></span><br><span class="line">| tname  |</span><br><span class="line">+<span class="comment">--------+</span></span><br><span class="line">| 王萍   |</span><br><span class="line">+<span class="comment">--------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 23.查询95033班和95031班全体学生的记录.</span></span><br><span class="line"><span class="comment">-- 为了效果,增加一些数据</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student <span class="keyword">values</span>(<span class="string">'110'</span>, <span class="string">'张飞'</span>, <span class="string">'男'</span>, <span class="string">'1974-06-03'</span>, <span class="string">'95038'</span>);</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> student <span class="keyword">where</span> <span class="keyword">class</span> <span class="keyword">in</span>(<span class="string">'95031'</span>, <span class="string">'95033'</span>);</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br><span class="line">| sno | sname     | sgender | sbirthday           | class |</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br><span class="line">| 101 | 曾华      | 男      | 1977-09-01 00:00:00 | 95033 |</span><br><span class="line">| 102 | 匡明      | 男      | 1975-10-02 00:00:00 | 95031 |</span><br><span class="line">| 103 | 王丽      | 女      | 1976-01-23 00:00:00 | 95033 |</span><br><span class="line">| 104 | 李军      | 男      | 1976-02-20 00:00:00 | 95033 |</span><br><span class="line">| 105 | 王芳      | 女      | 1975-02-10 00:00:00 | 95031 |</span><br><span class="line">| 106 | 陆君      | 男      | 1974-06-03 00:00:00 | 95031 |</span><br><span class="line">| 107 | 王尼玛    | 男      | 1976-02-20 00:00:00 | 95033 |</span><br><span class="line">| 108 | 张全蛋    | 男      | 1975-02-10 00:00:00 | 95031 |</span><br><span class="line">| 109 | 赵铁柱    | 男      | 1974-06-03 00:00:00 | 95031 |</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 24.查询存在有85分以上成绩的课程cno</span></span><br><span class="line"><span class="keyword">select</span> cno, degree <span class="keyword">from</span> score <span class="keyword">where</span> degree &gt; <span class="number">85</span>;</span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 25.查询出"计算机系"教师所教课程的成绩表</span></span><br><span class="line"><span class="comment">-- 先找出这个系老师的tno</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> teacher <span class="keyword">where</span> depart = <span class="string">'计算机系'</span>;</span><br><span class="line">+<span class="comment">-----+--------+---------+------------+-----------+--------------+</span></span><br><span class="line">| tno | tname  | tgender | tbirthday  | prof      | depart       |</span><br><span class="line">+<span class="comment">-----+--------+---------+------------+-----------+--------------+</span></span><br><span class="line">| 804 | 李诚   | 男      | 1958-12-02 | 副教授    | 计算机系     |</span><br><span class="line">| 825 | 王萍   | 女      | 1972-05-05 | 助教      | 计算机系     |</span><br><span class="line">+<span class="comment">-----+--------+---------+------------+-----------+--------------+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查出所教课程</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> course <span class="keyword">where</span> tno <span class="keyword">in</span>(<span class="keyword">select</span> tno <span class="keyword">from</span> teacher <span class="keyword">where</span> depart = <span class="string">'计算机系'</span>);</span><br><span class="line">+<span class="comment">-------+-----------------+-----+</span></span><br><span class="line">| cno   | cname           | tno |</span><br><span class="line">+<span class="comment">-------+-----------------+-----+</span></span><br><span class="line">| 3-245 | 操作系统        | 804 |</span><br><span class="line">| 3-105 | 计算机导论      | 825 |</span><br><span class="line">+<span class="comment">-------+-----------------+-----+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 最后查出成绩表</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> score <span class="keyword">where</span> cno <span class="keyword">in</span>(<span class="keyword">select</span> cno <span class="keyword">from</span> course <span class="keyword">where</span> tno <span class="keyword">in</span>(<span class="keyword">select</span> tno <span class="keyword">from</span> teacher <span class="keyword">where</span> depart = <span class="string">'计算机系'</span>));</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| sno | cno   | degree |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| 103 | 3-245 |     86 |</span><br><span class="line">| 105 | 3-245 |     75 |</span><br><span class="line">| 109 | 3-245 |     68 |</span><br><span class="line">| 101 | 3-105 |     90 |</span><br><span class="line">| 102 | 3-105 |     91 |</span><br><span class="line">| 103 | 3-105 |     92 |</span><br><span class="line">| 104 | 3-105 |     89 |</span><br><span class="line">| 105 | 3-105 |     88 |</span><br><span class="line">| 109 | 3-105 |     76 |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 26.查询"计算机系"与"电子工程系"不同职称的教师的tname和prof</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> teacher <span class="keyword">where</span> depart = <span class="string">'计算机系'</span> <span class="keyword">and</span> prof <span class="keyword">not</span> <span class="keyword">in</span>(<span class="keyword">select</span> prof <span class="keyword">from</span> teacher <span class="keyword">where</span> depart = <span class="string">'电子工程系'</span>);</span><br><span class="line">+<span class="comment">-----+--------+---------+------------+-----------+--------------+</span></span><br><span class="line">| tno | tname  | tgender | tbirthday  | prof      | depart       |</span><br><span class="line">+<span class="comment">-----+--------+---------+------------+-----------+--------------+</span></span><br><span class="line">| 804 | 李诚   | 男      | 1958-12-02 | 副教授    | 计算机系     |</span><br><span class="line">+<span class="comment">-----+--------+---------+------------+-----------+--------------+</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> teacher <span class="keyword">where</span> depart = <span class="string">'电子工程系'</span> <span class="keyword">and</span> prof <span class="keyword">not</span> <span class="keyword">in</span>(<span class="keyword">select</span> prof <span class="keyword">from</span> teacher <span class="keyword">where</span> depart = <span class="string">'计算机系'</span>);</span><br><span class="line">+<span class="comment">-----+--------+---------+------------+--------+-----------------+</span></span><br><span class="line">| tno | tname  | tgender | tbirthday  | prof   | depart          |</span><br><span class="line">+<span class="comment">-----+--------+---------+------------+--------+-----------------+</span></span><br><span class="line">| 856 | 张旭   | 男      | 1969-03-12 | 讲师   | 电子工程系      |</span><br><span class="line">+<span class="comment">-----+--------+---------+------------+--------+-----------------+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- union 联合 求并集</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> teacher <span class="keyword">where</span> depart = <span class="string">'计算机系'</span> <span class="keyword">and</span> prof <span class="keyword">not</span> <span class="keyword">in</span>(<span class="keyword">select</span> prof <span class="keyword">from</span> teacher <span class="keyword">where</span> depart = <span class="string">'电子工程系'</span>)</span><br><span class="line"><span class="keyword">union</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> teacher <span class="keyword">where</span> depart = <span class="string">'电子工程系'</span> <span class="keyword">and</span> prof <span class="keyword">not</span> <span class="keyword">in</span>(<span class="keyword">select</span> prof <span class="keyword">from</span> teacher <span class="keyword">where</span> depart = <span class="string">'计算机系'</span>);</span><br><span class="line">+<span class="comment">-----+--------+---------+------------+-----------+-----------------+</span></span><br><span class="line">| tno | tname  | tgender | tbirthday  | prof      | depart          |</span><br><span class="line">+<span class="comment">-----+--------+---------+------------+-----------+-----------------+</span></span><br><span class="line">| 804 | 李诚   | 男      | 1958-12-02 | 副教授    | 计算机系        |</span><br><span class="line">| 856 | 张旭   | 男      | 1969-03-12 | 讲师      | 电子工程系      |</span><br><span class="line">+<span class="comment">-----+--------+---------+------------+-----------+-----------------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 27.查询选修编号为"3-105"课程且成绩至少高于选修编号为"3-245"的同学的cno,sno和degree</span></span><br><span class="line"><span class="comment">-- 并按degree从高到低次序排序</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> score <span class="keyword">where</span> cno = <span class="string">'3-245'</span>;</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| sno | cno   | degree |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| 103 | 3-245 |     86 |</span><br><span class="line">| 105 | 3-245 |     75 |</span><br><span class="line">| 109 | 3-245 |     68 |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> score <span class="keyword">where</span> cno = <span class="string">'3-105'</span>;</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| sno | cno   | degree |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| 101 | 3-105 |     90 |</span><br><span class="line">| 102 | 3-105 |     91 |</span><br><span class="line">| 103 | 3-105 |     92 |</span><br><span class="line">| 104 | 3-105 |     89 |</span><br><span class="line">| 105 | 3-105 |     88 |</span><br><span class="line">| 109 | 3-105 |     76 |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 至少,即大于其中任意一个,用any</span></span><br><span class="line"><span class="comment">-- any 符合sql语句任意一个条件即可</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> score </span><br><span class="line"><span class="keyword">where</span> cno = <span class="string">'3-105'</span> </span><br><span class="line"><span class="keyword">and</span> degree &gt; <span class="keyword">any</span>(<span class="keyword">select</span> degree <span class="keyword">from</span> score <span class="keyword">where</span> cno = <span class="string">'3-245'</span>)</span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> degree <span class="keyword">desc</span>;</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| sno | cno   | degree |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| 103 | 3-105 |     92 |</span><br><span class="line">| 102 | 3-105 |     91 |</span><br><span class="line">| 101 | 3-105 |     90 |</span><br><span class="line">| 104 | 3-105 |     89 |</span><br><span class="line">| 105 | 3-105 |     88 |</span><br><span class="line">| 109 | 3-105 |     76 |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 28.查询选修编号为"3-105"且成绩高于选修编号为"3-245"课程的同学的cno,sno和degree</span></span><br><span class="line"><span class="comment">-- all 需要负荷sql语句的所有条件</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> score </span><br><span class="line"><span class="keyword">where</span> cno = <span class="string">'3-105'</span> </span><br><span class="line"><span class="keyword">and</span> degree &gt; <span class="keyword">all</span>(<span class="keyword">select</span> degree <span class="keyword">from</span> score <span class="keyword">where</span> cno = <span class="string">'3-245'</span>);</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| sno | cno   | degree |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| 101 | 3-105 |     90 |</span><br><span class="line">| 102 | 3-105 |     91 |</span><br><span class="line">| 103 | 3-105 |     92 |</span><br><span class="line">| 104 | 3-105 |     89 |</span><br><span class="line">| 105 | 3-105 |     88 |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 29.查询所有教师和同学的name,gender和birthday</span></span><br><span class="line"><span class="comment">-- 别名 as</span></span><br><span class="line"><span class="keyword">select</span> tname <span class="keyword">as</span> <span class="keyword">name</span>, tgender <span class="keyword">as</span> gender, tbirthday <span class="keyword">as</span> birthday <span class="keyword">from</span> teacher </span><br><span class="line"><span class="keyword">union</span> </span><br><span class="line"><span class="keyword">select</span> sname, sgender, sbirthday <span class="keyword">from</span> student;</span><br><span class="line">+<span class="comment">-----------+--------+---------------------+</span></span><br><span class="line">| name      | gender | birthday            |</span><br><span class="line">+<span class="comment">-----------+--------+---------------------+</span></span><br><span class="line">| 李诚      | 男     | 1958-12-02          |</span><br><span class="line">| 王萍      | 女     | 1972-05-05          |</span><br><span class="line">| 刘冰      | 女     | 1977-08-14          |</span><br><span class="line">| 张旭      | 男     | 1969-03-12          |</span><br><span class="line">| 曾华      | 男     | 1977-09-01 00:00:00 |</span><br><span class="line">| 匡明      | 男     | 1975-10-02 00:00:00 |</span><br><span class="line">| 王丽      | 女     | 1976-01-23 00:00:00 |</span><br><span class="line">| 李军      | 男     | 1976-02-20 00:00:00 |</span><br><span class="line">| 王芳      | 女     | 1975-02-10 00:00:00 |</span><br><span class="line">| 陆君      | 男     | 1974-06-03 00:00:00 |</span><br><span class="line">| 王尼玛    | 男     | 1976-02-20 00:00:00 |</span><br><span class="line">| 张全蛋    | 男     | 1975-02-10 00:00:00 |</span><br><span class="line">| 赵铁柱    | 男     | 1974-06-03 00:00:00 |</span><br><span class="line">| 张飞      | 男     | 1974-06-03 00:00:00 |</span><br><span class="line">+<span class="comment">-----------+--------+---------------------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 30.查询所有"女"教师和"女"同学的name,gender和birthday</span></span><br><span class="line"><span class="keyword">select</span> tname <span class="keyword">as</span> <span class="keyword">name</span>, tgender <span class="keyword">as</span> gender, tbirthday <span class="keyword">as</span> birthday <span class="keyword">from</span> teacher <span class="keyword">where</span> tgender = <span class="string">'女'</span> </span><br><span class="line"><span class="keyword">union</span> </span><br><span class="line"><span class="keyword">select</span> sname, sgender, sbirthday <span class="keyword">from</span> student <span class="keyword">where</span> sgender = <span class="string">'女'</span>;</span><br><span class="line">+<span class="comment">--------+--------+---------------------+</span></span><br><span class="line">| name   | gender | birthday            |</span><br><span class="line">+<span class="comment">--------+--------+---------------------+</span></span><br><span class="line">| 王萍   | 女     | 1972-05-05          |</span><br><span class="line">| 刘冰   | 女     | 1977-08-14          |</span><br><span class="line">| 王丽   | 女     | 1976-01-23 00:00:00 |</span><br><span class="line">| 王芳   | 女     | 1975-02-10 00:00:00 |</span><br><span class="line">+<span class="comment">--------+--------+---------------------+</span></span><br></pre></td></tr></table></figure>
<p><strong>复制表数据做条件查询:</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 31.查询成绩比该课程平均成绩低的同学的成绩表</span></span><br><span class="line"><span class="keyword">select</span> cno, <span class="keyword">avg</span>(degree) <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> cno;</span><br><span class="line">+<span class="comment">-------+-------------+</span></span><br><span class="line">| cno   | avg(degree) |</span><br><span class="line">+<span class="comment">-------+-------------+</span></span><br><span class="line">| 3-105 |     87.6667 |</span><br><span class="line">| 3-245 |     76.3333 |</span><br><span class="line">| 6-166 |     81.6667 |</span><br><span class="line">+<span class="comment">-------+-------------+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 注意:这里的score a 的意思和score as a一样</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> score a <span class="keyword">where</span> degree &lt; (<span class="keyword">select</span> <span class="keyword">avg</span>(degree) <span class="keyword">from</span> score b <span class="keyword">where</span> a.cno = b.cno);</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| sno | cno   | degree |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| 105 | 3-245 |     75 |</span><br><span class="line">| 105 | 6-166 |     79 |</span><br><span class="line">| 109 | 3-105 |     76 |</span><br><span class="line">| 109 | 3-245 |     68 |</span><br><span class="line">| 109 | 6-166 |     81 |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 32.查询所有任课老师的tname和depart</span></span><br><span class="line"><span class="comment">-- 课程表中安排了课程的教师</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> course;</span><br><span class="line">+<span class="comment">-------+-----------------+-----+</span></span><br><span class="line">| cno   | cname           | tno |</span><br><span class="line">+<span class="comment">-------+-----------------+-----+</span></span><br><span class="line">| 3-105 | 计算机导论      | 825 |</span><br><span class="line">| 3-245 | 操作系统        | 804 |</span><br><span class="line">| 6-166 | 数字电路        | 856 |</span><br><span class="line">| 9-888 | 高等数学        | 831 |</span><br><span class="line">+<span class="comment">-------+-----------------+-----+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 找出这些老师的tname和depart</span></span><br><span class="line"><span class="keyword">select</span> tname, depart <span class="keyword">from</span> teacher <span class="keyword">where</span> tno <span class="keyword">in</span>(<span class="keyword">select</span> tno <span class="keyword">from</span> course);</span><br><span class="line">+<span class="comment">--------+-----------------+</span></span><br><span class="line">| tname  | depart          |</span><br><span class="line">+<span class="comment">--------+-----------------+</span></span><br><span class="line">| 李诚   | 计算机系        |</span><br><span class="line">| 王萍   | 计算机系        |</span><br><span class="line">| 刘冰   | 电子工程系      |</span><br><span class="line">| 张旭   | 电子工程系      |</span><br><span class="line">+<span class="comment">--------+-----------------+</span></span><br></pre></td></tr></table></figure>
<p><strong>条件加分组筛选:</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 33.查询至少有2名男生的班号</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">class</span> <span class="keyword">from</span> student <span class="keyword">where</span> sgender = <span class="string">'男'</span> <span class="keyword">group</span> <span class="keyword">by</span> <span class="keyword">class</span> <span class="keyword">having</span> <span class="keyword">count</span>(*) &gt; <span class="number">1</span>;</span><br><span class="line">+<span class="comment">-------+</span></span><br><span class="line">| class |</span><br><span class="line">+<span class="comment">-------+</span></span><br><span class="line">| 95031 |</span><br><span class="line">| 95033 |</span><br><span class="line">+<span class="comment">-------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 34.查询student表中不姓"王"的同学记录</span></span><br><span class="line"><span class="comment">-- not like 模糊查询取反</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> student <span class="keyword">where</span> sname <span class="keyword">not</span> <span class="keyword">like</span> <span class="string">'王%'</span>;</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br><span class="line">| sno | sname     | sgender | sbirthday           | class |</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br><span class="line">| 101 | 曾华      | 男      | 1977-09-01 00:00:00 | 95033 |</span><br><span class="line">| 102 | 匡明      | 男      | 1975-10-02 00:00:00 | 95031 |</span><br><span class="line">| 104 | 李军      | 男      | 1976-02-20 00:00:00 | 95033 |</span><br><span class="line">| 106 | 陆君      | 男      | 1974-06-03 00:00:00 | 95031 |</span><br><span class="line">| 108 | 张全蛋    | 男      | 1975-02-10 00:00:00 | 95031 |</span><br><span class="line">| 109 | 赵铁柱    | 男      | 1974-06-03 00:00:00 | 95031 |</span><br><span class="line">| 110 | 张飞      | 男      | 1974-06-03 00:00:00 | 95038 |</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 35.查询student表中每个学生的姓名和年龄</span></span><br><span class="line"><span class="comment">-- 年龄:当前年份 - 出生年份</span></span><br><span class="line"><span class="comment">-- 当前年份:</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">year</span>(<span class="keyword">now</span>());</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 出生年份</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">year</span>(sbirthday) <span class="keyword">from</span> student;</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> sname, <span class="keyword">year</span>(<span class="keyword">now</span>()) - <span class="keyword">year</span>(sbirthday) <span class="keyword">as</span> <span class="string">'年龄'</span> <span class="keyword">from</span> student;</span><br><span class="line">+<span class="comment">-----------+--------+</span></span><br><span class="line">| sname     | 年龄   |</span><br><span class="line">+<span class="comment">-----------+--------+</span></span><br><span class="line">| 曾华      |     43 |</span><br><span class="line">| 匡明      |     45 |</span><br><span class="line">| 王丽      |     44 |</span><br><span class="line">| 李军      |     44 |</span><br><span class="line">| 王芳      |     45 |</span><br><span class="line">| 陆君      |     46 |</span><br><span class="line">| 王尼玛    |     44 |</span><br><span class="line">| 张全蛋    |     45 |</span><br><span class="line">| 赵铁柱    |     46 |</span><br><span class="line">| 张飞      |     46 |</span><br><span class="line">+<span class="comment">-----------+--------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 36.查询student表中最大和最小的sbirthday日期值</span></span><br><span class="line"><span class="keyword">select</span> sbirthday <span class="keyword">from</span> student <span class="keyword">order</span> <span class="keyword">by</span> sbirthday;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- max min</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">max</span>(sbirthday) <span class="keyword">as</span> <span class="string">'最大'</span>, <span class="keyword">min</span>(sbirthday) <span class="keyword">as</span> <span class="string">'最小'</span> <span class="keyword">from</span> student; </span><br><span class="line">+<span class="comment">---------------------+---------------------+</span></span><br><span class="line">| 最大                | 最小                |</span><br><span class="line">+<span class="comment">---------------------+---------------------+</span></span><br><span class="line">| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |</span><br><span class="line">+<span class="comment">---------------------+---------------------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 37.以班号和年龄从大到小的顺序查询student表中的全部记录</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> student <span class="keyword">order</span> <span class="keyword">by</span> <span class="keyword">class</span> <span class="keyword">desc</span>, sbirthday;</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br><span class="line">| sno | sname     | sgender | sbirthday           | class |</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br><span class="line">| 110 | 张飞      | 男      | 1974-06-03 00:00:00 | 95038 |</span><br><span class="line">| 103 | 王丽      | 女      | 1976-01-23 00:00:00 | 95033 |</span><br><span class="line">| 104 | 李军      | 男      | 1976-02-20 00:00:00 | 95033 |</span><br><span class="line">| 107 | 王尼玛    | 男      | 1976-02-20 00:00:00 | 95033 |</span><br><span class="line">| 101 | 曾华      | 男      | 1977-09-01 00:00:00 | 95033 |</span><br><span class="line">| 106 | 陆君      | 男      | 1974-06-03 00:00:00 | 95031 |</span><br><span class="line">| 109 | 赵铁柱    | 男      | 1974-06-03 00:00:00 | 95031 |</span><br><span class="line">| 105 | 王芳      | 女      | 1975-02-10 00:00:00 | 95031 |</span><br><span class="line">| 108 | 张全蛋    | 男      | 1975-02-10 00:00:00 | 95031 |</span><br><span class="line">| 102 | 匡明      | 男      | 1975-10-02 00:00:00 | 95031 |</span><br><span class="line">+<span class="comment">-----+-----------+---------+---------------------+-------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 38.查询'男'教师及其所上的课程</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> teacher <span class="keyword">where</span> tgender = <span class="string">'男'</span>;</span><br><span class="line">+<span class="comment">-----+--------+---------+------------+-----------+-----------------+</span></span><br><span class="line">| tno | tname  | tgender | tbirthday  | prof      | depart          |</span><br><span class="line">+<span class="comment">-----+--------+---------+------------+-----------+-----------------+</span></span><br><span class="line">| 804 | 李诚   | 男      | 1958-12-02 | 副教授    | 计算机系        |</span><br><span class="line">| 856 | 张旭   | 男      | 1969-03-12 | 讲师      | 电子工程系      |</span><br><span class="line">+<span class="comment">-----+--------+---------+------------+-----------+-----------------+</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> course <span class="keyword">where</span> tno <span class="keyword">in</span>(<span class="keyword">select</span> tno <span class="keyword">from</span> teacher <span class="keyword">where</span> tgender = <span class="string">'男'</span>);</span><br><span class="line">+<span class="comment">-------+--------------+-----+</span></span><br><span class="line">| cno   | cname        | tno |</span><br><span class="line">+<span class="comment">-------+--------------+-----+</span></span><br><span class="line">| 3-245 | 操作系统     | 804 |</span><br><span class="line">| 6-166 | 数字电路     | 856 |</span><br><span class="line">+<span class="comment">-------+--------------+-----+</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 加上教师姓名</span></span><br><span class="line"><span class="keyword">select</span> tname, tgender,cname <span class="keyword">from</span> course, teacher <span class="keyword">where</span> course.tno = teacher.tno <span class="keyword">and</span> tgender = <span class="string">'男'</span>;</span><br><span class="line">+<span class="comment">--------+---------+--------------+</span></span><br><span class="line">| tname  | tgender | cname        |</span><br><span class="line">+<span class="comment">--------+---------+--------------+</span></span><br><span class="line">| 李诚   | 男      | 操作系统     |</span><br><span class="line">| 张旭   | 男      | 数字电路     |</span><br><span class="line">+<span class="comment">--------+---------+--------------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">- 39.查询最高分同学的sno,cno和degree列</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> score <span class="keyword">where</span> degree = (<span class="keyword">select</span> <span class="keyword">max</span>(degree) <span class="keyword">from</span> score);</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| sno | cno   | degree |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| 103 | 3-105 |     92 |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 40.查询和"李军"同性别的所有同学的sname</span></span><br><span class="line"><span class="keyword">select</span> sname <span class="keyword">from</span> student <span class="keyword">where</span> sgender = (<span class="keyword">select</span> sgender <span class="keyword">from</span> student <span class="keyword">where</span> sname = <span class="string">'李军'</span>);</span><br><span class="line">+<span class="comment">-----------+</span></span><br><span class="line">| sname     |</span><br><span class="line">+<span class="comment">-----------+</span></span><br><span class="line">| 曾华      |</span><br><span class="line">| 匡明      |</span><br><span class="line">| 李军      |</span><br><span class="line">| 陆君      |</span><br><span class="line">| 王尼玛    |</span><br><span class="line">| 张全蛋    |</span><br><span class="line">| 赵铁柱    |</span><br><span class="line">| 张飞      |</span><br><span class="line">+<span class="comment">-----------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 41.查询和"李军"同性别并同班的同学sname</span></span><br><span class="line"><span class="keyword">select</span> sname <span class="keyword">from</span> student </span><br><span class="line"><span class="keyword">where</span> sgender = (<span class="keyword">select</span> sgender <span class="keyword">from</span> student <span class="keyword">where</span> sname = <span class="string">'李军'</span>) </span><br><span class="line"><span class="keyword">and</span> <span class="keyword">class</span> = (<span class="keyword">select</span> <span class="keyword">class</span> <span class="keyword">from</span> student <span class="keyword">where</span> sname = <span class="string">'李军'</span>);</span><br><span class="line">+<span class="comment">-----------+</span></span><br><span class="line">| sname     |</span><br><span class="line">+<span class="comment">-----------+</span></span><br><span class="line">| 曾华      |</span><br><span class="line">| 李军      |</span><br><span class="line">| 王尼玛    |</span><br><span class="line">+<span class="comment">-----------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 42.查询所有选修"计算机导论"课程的"男"同学的成绩表</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> score </span><br><span class="line"><span class="keyword">where</span> cno = (<span class="keyword">select</span> cno <span class="keyword">from</span> course <span class="keyword">where</span> cname = <span class="string">'计算机导论'</span>) </span><br><span class="line"><span class="keyword">and</span> sno <span class="keyword">in</span> (<span class="keyword">select</span> sno <span class="keyword">from</span> student <span class="keyword">where</span> sgender = <span class="string">'男'</span>);</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| sno | cno   | degree |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br><span class="line">| 101 | 3-105 |     90 |</span><br><span class="line">| 102 | 3-105 |     91 |</span><br><span class="line">| 104 | 3-105 |     89 |</span><br><span class="line">| 109 | 3-105 |     76 |</span><br><span class="line">+<span class="comment">-----+-------+--------+</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 43.假设使用如下命令建立类一个grade表</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> grade(</span><br><span class="line">    <span class="keyword">low</span> <span class="built_in">int</span>(<span class="number">3</span>),</span><br><span class="line">    upp <span class="built_in">int</span>(<span class="number">3</span>),</span><br><span class="line">    grade <span class="built_in">char</span>(<span class="number">1</span>)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> grade <span class="keyword">values</span>(<span class="number">90</span>, <span class="number">100</span>, <span class="string">'A'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> grade <span class="keyword">values</span>(<span class="number">80</span>, <span class="number">89</span>, <span class="string">'B'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> grade <span class="keyword">values</span>(<span class="number">70</span>, <span class="number">79</span>, <span class="string">'C'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> grade <span class="keyword">values</span>(<span class="number">60</span>, <span class="number">69</span>, <span class="string">'D'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> grade <span class="keyword">values</span>(<span class="number">0</span>, <span class="number">59</span>, <span class="string">'E'</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 现查询所有同学的sno,cno和grade列</span></span><br><span class="line"><span class="keyword">select</span> sno, cno, degree, grade <span class="keyword">from</span> score, grade <span class="keyword">where</span> degree <span class="keyword">between</span> <span class="keyword">low</span> <span class="keyword">and</span> upp;</span><br><span class="line">+<span class="comment">-----+-------+--------+-------+</span></span><br><span class="line">| sno | cno   | degree | grade |</span><br><span class="line">+<span class="comment">-----+-------+--------+-------+</span></span><br><span class="line">| 101 | 3-105 |     90 | A     |</span><br><span class="line">| 102 | 3-105 |     91 | A     |</span><br><span class="line">| 103 | 3-105 |     92 | A     |</span><br><span class="line">| 103 | 3-245 |     86 | B     |</span><br><span class="line">| 103 | 6-166 |     85 | B     |</span><br><span class="line">| 104 | 3-105 |     89 | B     |</span><br><span class="line">| 105 | 3-105 |     88 | B     |</span><br><span class="line">| 105 | 3-245 |     75 | C     |</span><br><span class="line">| 105 | 6-166 |     79 | C     |</span><br><span class="line">| 109 | 3-105 |     76 | C     |</span><br><span class="line">| 109 | 3-245 |     68 | D     |</span><br><span class="line">| 109 | 6-166 |     81 | B     |</span><br><span class="line">+<span class="comment">-----+-------+--------+-------+</span></span><br></pre></td></tr></table></figure>

<h2 id="参考资料："><a href="#参考资料：" class="headerlink" title="参考资料："></a>参考资料：</h2><ol>
<li>B站视频<a href="https://www.bilibili.com/video/BV1Vt411z7wy" target="_blank" rel="noopener">“一天学会 MySQL 数据库”</a></li>
<li><a href="https://cc-51760.oss-cn-beijing.aliyuncs.com/MYSQL%E7%AC%94%E8%AE%B0.zip" target="_blank" rel="noopener">大佬笔记1</a></li>
<li><a href="https://github.com/hjzCy/sql_node/blob/master/mysql/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0.md" target="_blank" rel="noopener">大佬笔记2</a></li>
</ol>

          
            <div class='article_footer'>
              
                
  
    
    



  

  
    
    



  

  
    
    

<section class="widget copyright  desktop mobile">
  <div class='content'>
    
      <blockquote>
        
          
            <p>本文由CyChan创作，转载请注明出处</p>

          
        
          
            <p>博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议</p>

          
        
          
            <p>本文永久链接是：<a href=https://cychan811.gitee.io/2020/07/04/MySQL%E7%AC%94%E8%AE%B0-2-%E6%9F%A5%E8%AF%A2%E7%BB%83%E4%B9%A0/>https://cychan811.gitee.io/2020/07/04/MySQL%E7%AC%94%E8%AE%B0-2-%E6%9F%A5%E8%AF%A2%E7%BB%83%E4%B9%A0/</a></p>
          
        
      </blockquote>
    
  </div>
</section>

  

  
    
    

<section class="widget qrcode  desktop mobile">
  

  <div class='content article-entry'>
    
      
        <div class='fancybox'><img src='https://gitee.com/CyChan811/BlogImage/raw/master/img/20200704103702.png'
        
          height='128px'
        ></div>
      
    
  </div>
</section>

  


              
            </div>
          
        </div>
        
          


  <section class='meta' id="footer-meta">
    <div class='new-meta-box'>
      
        
          <div class="new-meta-item date" itemprop="dateUpdated" datetime="2020-07-06T22:37:40+08:00">
  <a class='notlink'>
    <i class="fas fa-edit fa-fw" aria-hidden="true"></i>
    <p>更新于：2020年7月6日</p>
  </a>
</div>

        
      
        
          
  
  <div class="new-meta-item meta-tags"><a class="tag" href="/tags/MySQL/" rel="nofollow"><i class="fas fa-hashtag fa-fw" aria-hidden="true"></i><p>MySQL</p></a></div>


        
      
        
          

        
      
        
          
  <div class="new-meta-item share -mob-share-list">
  <div class="-mob-share-list share-body">
    
      
        <a class="-mob-share-qq" title="" rel="external nofollow noopener noreferrer"
          
          href="http://connect.qq.com/widget/shareqq/index.html?url=https://cychan811.gitee.io/2020/07/04/MySQL%E7%AC%94%E8%AE%B0-2-%E6%9F%A5%E8%AF%A2%E7%BB%83%E4%B9%A0/&title=MySQL笔记(2):查询练习 - CyChan's Blog&summary=mysql查询练习创建数据表"
          
          >
          
            <img src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-assets/logo/128/qq.png">
          
        </a>
      
    
      
        <a class="-mob-share-qzone" title="" rel="external nofollow noopener noreferrer"
          
          href="https://sns.qzone.qq.com/cgi-bin/qzshare/cgi_qzshare_onekey?url=https://cychan811.gitee.io/2020/07/04/MySQL%E7%AC%94%E8%AE%B0-2-%E6%9F%A5%E8%AF%A2%E7%BB%83%E4%B9%A0/&title=MySQL笔记(2):查询练习 - CyChan's Blog&summary=mysql查询练习创建数据表"
          
          >
          
            <img src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-assets/logo/128/qzone.png">
          
        </a>
      
    
      
        <a class="-mob-share-weibo" title="" rel="external nofollow noopener noreferrer"
          
          href="http://service.weibo.com/share/share.php?url=https://cychan811.gitee.io/2020/07/04/MySQL%E7%AC%94%E8%AE%B0-2-%E6%9F%A5%E8%AF%A2%E7%BB%83%E4%B9%A0/&title=MySQL笔记(2):查询练习 - CyChan's Blog&summary=mysql查询练习创建数据表"
          
          >
          
            <img src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-assets/logo/128/weibo.png">
          
        </a>
      
    
  </div>
</div>



        
      
    </div>
  </section>


        
        
          <div class="prev-next">
            
              <a class='prev' href='/2020/07/04/MySQL%E7%AC%94%E8%AE%B0-3-%E8%BF%9E%E6%8E%A5%E6%9F%A5%E8%AF%A2/'>
                <p class='title'><i class="fas fa-chevron-left" aria-hidden="true"></i>MySQL笔记(3):连接查询</p>
                <p class='content'>mysqld的四种连接查询– 创建数据库

create database testJoin;
– 创建两个表– person表
12345678-- id,-- name,-- cardIdc...</p>
              </a>
            
            
              <a class='next' href='/2020/07/04/MySQL%E7%AC%94%E8%AE%B0-1-%E7%BB%88%E7%AB%AF%E5%9F%BA%E6%9C%AC%E6%93%8D%E4%BD%9C/'>
                <p class='title'>MySQL笔记(1):终端基本操作<i class="fas fa-chevron-right" aria-hidden="true"></i></p>
                <p class='content'>一.登录和退出数据库服务器
登录:$ mysql -uroot -p123456  
退出:exit;



二.基本语法– 显示所有数据库show databases;
– 创建数据库crea...</p>
              </a>
            
          </div>
        
      </section>
    </article>
  

  
    <!-- 显示推荐文章和评论 -->



  <article class="post white-box reveal comments shadow">
    <section class="article typo">
      <p ct><i class='fas fa-comments'></i> 评论</p>
      
      
      
      
      
      
        <section id="comments">
          <div id="valine_container" class="valine_thread">
            <i class="fas fa-cog fa-spin fa-fw fa-2x"></i>
          </div>
        </section>
      
      
    </section>
  </article>


  




<!-- 根据页面mathjax变量决定是否加载MathJax数学公式js -->



  <script>
    window.subData = {
      title: 'MySQL笔记(2):查询练习',
      tools: true
    }
  </script>


</div>
<aside class='l_side'>
  
  

  
    
    



  <section class="widget toc-wrapper shadow desktop mobile" id="toc-div" >
    
  <header>
    
      <i class="fas fa-list fa-fw" aria-hidden="true"></i><span class='name'>本文目录</span>
    
  </header>


    <div class='content'>
        <ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#mysql查询练习"><span class="toc-text">mysql查询练习</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#创建数据表"><span class="toc-text">创建数据表</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#往数据表中添加数据"><span class="toc-text">往数据表中添加数据</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#查询练习"><span class="toc-text">查询练习</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#参考资料："><span class="toc-text">参考资料：</span></a></li></ol>
    </div>
  </section>


  


</aside>


  
  <footer class="clearfix">
    <br><br>
    
      
        <div class="aplayer-container">
          

  
    <meting-js
      theme='#1BCDFC'
      autoplay='false'
      volume='0.7'
      loop='all'
      order='random'
      fixed='false'
      list-max-height='340px'
      server='netease'
      type='playlist'
      id='2985312517'
      list-folded='true'>
    </meting-js>
  


        </div>
      
    
      
        <br>
        <div class="social-wrapper">
          
            
              <a href="mailto:1342736410@qq.com"
                class="social fas fa-envelope flat-btn"
                target="_blank"
                rel="external nofollow noopener noreferrer">
              </a>
            
          
            
              <a href="https://github.com/CyChan811"
                class="social fab fa-github flat-btn"
                target="_blank"
                rel="external nofollow noopener noreferrer">
              </a>
            
          
            
              <a href="http://wpa.qq.com/msgrd?v=3&amp;uin=1342736410&amp;site=qq&amp;menu=yes"
                class="social fab fa-qq flat-btn"
                target="_blank"
                rel="external nofollow noopener noreferrer">
              </a>
            
          
            
              <a href="http://music.163.com/playlist?id=2985312517&amp;userid=1980577201"
                class="social fas fa-headphones-alt flat-btn"
                target="_blank"
                rel="external nofollow noopener noreferrer">
              </a>
            
          
        </div>
      
    
      
        <div><p>博客内容遵循 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.zh" target="_blank" rel="noopener">署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议</a></p>
        </div>
      
    
      
        本站使用
        <a href="https://volantis.js.org/" target="_blank" class="codename">volantis</a>
        作为主题，总访问量为
          <span id="busuanzi_value_site_pv"><i class="fas fa-circle-notch fa-spin fa-fw" aria-hidden="true"></i></span>
          次
      
    
      
        
      
    
      
      <br>
        <span id="timeDate"></span><span id="times"></span>
        <div class='copyright'>
          <a href="https://cychan811.gitee.io">Copyright © 2017-2020 CyChan</a>
        </div>
      
    
    
  </footer>


      <script>
      var now = new Date(); 
      function createtime() { 
          var grt= new Date("07/01/2020 12:00:00");//在此处修改你的建站时间，格式：月/日/年 时:分:秒
          now.setTime(now.getTime()+250); 
          days = (now - grt ) / 1000 / 60 / 60 / 24; dnum = Math.floor(days); 
          hours = (now - grt ) / 1000 / 60 / 60 - (24 * dnum); hnum = Math.floor(hours); 
          if(String(hnum).length ==1 ){hnum = "0" + hnum;} minutes = (now - grt ) / 1000 /60 - (24 * 60 * dnum) - (60 * hnum); 
          mnum = Math.floor(minutes); if(String(mnum).length ==1 ){mnum = "0" + mnum;} 
          seconds = (now - grt ) / 1000 - (24 * 60 * 60 * dnum) - (60 * 60 * hnum) - (60 * mnum); 
          snum = Math.round(seconds); if(String(snum).length ==1 ){snum = "0" + snum;} 
          document.getElementById("timeDate").innerHTML = "本站已安全运行 "+dnum+" 天 "+ hnum + " 小时 " + mnum + " 分 " + snum + " 秒"; 
      } 
        setInterval("createtime()",250);
     </script>
<script>setLoadingBarProgress(80);</script>


      <script>setLoadingBarProgress(60);</script>
    </div>
    <a class="s-top fas fa-arrow-up fa-fw" href='javascript:void(0)'></a>
  </div>
  
<script src="https://cdn.jsdelivr.net/npm/jquery@3.4/dist/jquery.min.js"></script>


  <script>
    
    var SEARCH_SERVICE = "hexo" || "hexo";
    var ROOT = "/" || "/";
    if (!ROOT.endsWith('/')) ROOT += '/';
  </script>





  <script async src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-volantis@2/js/instant_page.js" type="module" defer integrity="sha384-OeDn4XE77tdHo8pGtE1apMPmAipjoxUQ++eeJa6EtJCfHlvijigWiJpD7VDPWXV1"></script>


  <script src="https://cdn.jsdelivr.net/npm/scrollreveal@4.0.6/dist/scrollreveal.min.js"></script>
  <script type="text/javascript">
    $(function() {
      ScrollReveal().reveal('.l_main .reveal', {
        distance: '8px',
        duration: '800',
        interval: '100',
        scale: '1'
      });
    });
  </script>


  
<script src="https://cdn.jsdelivr.net/npm/node-waves@0.7.6/dist/waves.min.js"></script>

  <script type="text/javascript">
    $(function() {
      Waves.attach('.flat-btn', ['waves-button']);
      Waves.attach('.float-btn', ['waves-button', 'waves-float']);
      Waves.attach('.float-btn-light', ['waves-button', 'waves-float', 'waves-light']);
      Waves.attach('.flat-box', ['waves-block']);
      Waves.attach('.float-box', ['waves-block', 'waves-float']);
      Waves.attach('.waves-image');
      Waves.init();
    });
  </script>


  <script defer src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-busuanzi@2.3/js/busuanzi.pure.mini.js"></script>



  
  
  
    
<script src="https://cdn.jsdelivr.net/npm/jquery-backstretch@2.1.18/jquery.backstretch.min.js"></script>

    <script type="text/javascript">
      $(function(){
        var imgs=["https://cdn.jsdelivr.net/gh/xaoxuu/cdn-wallpaper/abstract/41F215B9-261F-48B4-80B5-4E86E165259E.jpeg", "https://cdn.jsdelivr.net/gh/xaoxuu/cdn-wallpaper/abstract/BBC19066-E176-47C2-9D22-48C81EE5DF6B.jpeg", "https://cdn.jsdelivr.net/gh/xaoxuu/cdn-wallpaper/abstract/B18FCBB3-67FD-48CC-B4F3-457BA145F17A.jpeg", "https://ss0.bdstatic.com/70cFuHSh_Q1YnxGkpoWK1HF6hhy/it/u=3799510479,4073126189&fm=26&gp=0.jpg", "https://ss3.bdstatic.com/70cFv8Sh_Q1YnxGkpoWK1HF6hhy/it/u=2883764232,1534773763&fm=26&gp=0.jpg", "https://timgsa.baidu.com/timg?image&quality=80&size=b9999_10000&sec=1593783783483&di=3ad7e4713031f3e60c3a8bab2c2aae02&imgtype=0&src=http%3A%2F%2Fcz-video-photo.oss-cn-beijing.aliyuncs.com%2F20191015%2F7d47db92bbe2627ecd035976a61463cb_00001.jpg", "https://timgsa.baidu.com/timg?image&quality=80&size=b9999_10000&sec=1593783631802&di=0616aa53378b2de7816f786a97c75bfe&imgtype=0&src=http%3A%2F%2Fpic1.win4000.com%2Fwallpaper%2F2018-01-10%2F5a55c62f9a142.jpg", "https://cdn.jsdelivr.net/gh/xaoxuu/cdn-wallpaper/abstract/00E0F0ED-9F1C-407A-9AA6-545649D919F4.jpeg", "https://cdn.jsdelivr.net/gh/zyoushuo/Blog/images/bj1.jpg", "https://gitee.com/CyChan811/BlogImage/raw/master/img/1 (2).png", "https://gitee.com/CyChan811/BlogImage/raw/master/img/t01e2fa7120d00d62da.jpg", "https://gitee.com/CyChan811/BlogImage/raw/master/img/1 (19).jpg"];
        if ('true' == 'true') {
          function shuffle(arr){
            /*From countercurrent-time*/
            var n = arr.length;
            while(n--) {
              var index = Math.floor(Math.random() * n);
              var temp = arr[index];
              arr[index] = arr[n];
              arr[n] = temp;
            }
          }
          shuffle(imgs);
        }
        if ('') {
          $('').backstretch(
            imgs,
          {
            duration: "20000",
            fade: "1500"
          });
        } else {
          $.backstretch(
            imgs,
          {
            duration: "20000",
            fade: "1500"
          });
        }
      });
    </script>
  



  
    
<script src="https://cdn.jsdelivr.net/npm/aplayer@1.10/dist/APlayer.min.js"></script>

  
    
<script src="https://cdn.jsdelivr.net/npm/meting@2.0/dist/Meting.min.js"></script>

  









  
    
<script src="https://cdn.jsdelivr.net/gh/HCLonely/Valine@latest/dist/Valine.min.js"></script>

  
  <script>
  var GUEST_INFO = ['nick','mail','link'];
  var meta = 'nick,mail,link'.split(',').filter(function(item){
    return GUEST_INFO.indexOf(item) > -1
  });
  var REQUIRED_FIELDS = ['nick','mail','link'];
  var requiredFields = 'nick,mail'.split(',').filter(function(item){
    return REQUIRED_FIELDS.indexOf(item) > -1
  });
  var valine = new Valine();
  var master = 'f03ef4cd1728b39a2b1d24cb0c7c4ac8'.split(',');
  function emoji(path, idx, ext) {
      return path + "/" + path + "-" + idx + "." + ext;
  }
  var emojiMaps = {};
  for (var i = 1; i <= 54; i++) {
    emojiMaps['tieba-' + i] = emoji('tieba', i, 'png');
  }
  for (var i = 1; i <= 101; i++) {
    emojiMaps['qq-' + i] = emoji('qq', i, 'gif');
  }
  for (var i = 1; i <= 116; i++) {
    emojiMaps['aru-' + i] = emoji('aru', i, 'gif');
  }
  for (var i = 1; i <= 125; i++) {
    emojiMaps['twemoji-' + i] = emoji('twemoji', i, 'png');
  }
  for (var i = 1; i <= 4; i++) {
    emojiMaps['weibo-' + i] = emoji('weibo', i, 'png');
  }
  valine.init({
    el: '#valine_container',
    meta: meta,
    
    appId: "COclWTHE9FOx0Kp5LQxypRuw-gzGzoHsz",
    appKey: "IFkkcvnaeUWqUUYoWqCJ5hUY",
    placeholder: "ヾﾉ≧∀≦)o来啊，快活啊!~",
    pageSize:'10',
    avatar:'robohash',
    lang:'zh-cn',
    visitor: 'true',
    highlight: 'true',
    mathJax: 'false',
    enableQQ: 'true',
    requiredFields: requiredFields,
    emojiCDN: 'https://cdn.jsdelivr.net/gh/xaoxuu/cdn-assets/emoji/valine/',
    emojiMaps: emojiMaps,
    master: master
  })
  </script>





  
<script src="/js/app.js"></script>



  
<script src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-volantis@2.6.5/js/search.js"></script>



  
<script src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-volantis@2/js/comment_typing.js"></script>






<!-- 复制 -->

  <script src="https://cdn.jsdelivr.net/npm/clipboard@2/dist/clipboard.min.js"></script>
<script>
  function wait(callback, seconds) {
    var timelag = null;
    timelag = window.setTimeout(callback, seconds);
  }
  !function (e, t, a) {
    var initCopyCode = function(){
      var copyHtml = '';
      copyHtml += '<button class="btn-copy" data-clipboard-snippet="">';
      copyHtml += '<i class="fas fa-copy"></i><span>COPY</span>';
      copyHtml += '</button>';
      $(".highlight .code pre").before(copyHtml);
      $(".article pre code").before(copyHtml);
      var clipboard = new ClipboardJS('.btn-copy', {
        target: function(trigger) {
          return trigger.nextElementSibling;
        }
      });
      clipboard.on('success', function(e) {
        let $btn = $(e.trigger);
        $btn.addClass('copied');
        let $icon = $($btn.find('i'));
        $icon.removeClass('fa-copy');
        $icon.addClass('fa-check-circle');
        let $span = $($btn.find('span'));
        $span[0].innerText = 'COPIED';
        
        wait(function () { // 等待两秒钟后恢复
          $icon.removeClass('fa-check-circle');
          $icon.addClass('fa-copy');
          $span[0].innerText = 'COPY';
        }, 2000);
      });
      clipboard.on('error', function(e) {
        e.clearSelection();
        let $btn = $(e.trigger);
        $btn.addClass('copy-failed');
        let $icon = $($btn.find('i'));
        $icon.removeClass('fa-copy');
        $icon.addClass('fa-times-circle');
        let $span = $($btn.find('span'));
        $span[0].innerText = 'COPY FAILED';
        
        wait(function () { // 等待两秒钟后恢复
          $icon.removeClass('fa-times-circle');
          $icon.addClass('fa-copy');
          $span[0].innerText = 'COPY';
        }, 2000);
      });
    }
    initCopyCode();
  }(window, document);
</script>




<!-- fancybox -->
<script src="https://cdn.jsdelivr.net/gh/fancyapps/fancybox@3.5.7/dist/jquery.fancybox.min.js"></script>
<script>
  function pjax_fancybox() {
    $(".article-entry").find("img").not('.inline').not('a img').each(function () { //渲染 fancybox
      var element = document.createElement("a"); // a 标签
      $(element).attr("pjax-fancybox", "");  // 过滤 pjax
      $(element).attr("href", $(this).attr("src"));
      if ($(this).attr("data-original")) {
        $(element).attr("href", $(this).attr("data-original"));
      }
      $(element).attr("data-fancybox", "images");
      var caption = "";   // 描述信息
      if ($(this).attr('alt')) {  // 标准 markdown 描述信息
        $(element).attr('data-caption', $(this).attr('alt'));
        caption = $(this).attr('alt');
      }
      var div = document.createElement("div");
      $(div).addClass("fancybox");
      $(this).wrap(div); // 最外层套 div ，其实主要作用还是 class 样式
      var span = document.createElement("span");
      $(span).addClass("image-caption");
      $(span).text(caption); // 加描述
      $(this).after(span);  // 再套一层描述
      $(this).wrap(element);  // 最后套 a 标签
    })
    $(".article-entry").find("img").fancybox({
      selector: '[data-fancybox="images"]',
      hash: false,
      loop: false,
      closeClick: true,
      helpers: {
        overlay: {closeClick: true}
      },
      buttons: [
        "zoom",
        "close"
      ]
    });
  };
  $(function () {
    pjax_fancybox();
  });
</script>





  <script>setLoadingBarProgress(100);</script>
  <script src="https://cdn.jsdelivr.net/gh/zyoushuo/Blog/hexo/js/mouse_click.js"></script>
</body>
</html>
